1 - Introduccion

Volver al Inicio

Bases de Datos SQL

Las Bases de datos SQL, son bases de datos del tipo Relacionales y estructuradas. Su nombre SQL proviene del término (Structured Query Language), en español: Lenguaje de Consulta Estructurado. Se operan a través de SQL, (“sícuel”), un lenguaje derivado del estándar ANSI SQL. El lenguaje con el cual se manipulan se conoce como Transact SQL aunque algunas alternativas comerciales utilizan su propia variante con mínimas diferencias sobre el lenguaje estandarizado ANSI SQL.

Bases de Datos No-SQL

Las bases de datos No-SQL (not only SQL) difieren del modelo estructurado, apoyándose en un sistema de almacenamiento del tipo {clave - valor}. No utilizan el lenguaje SQL para realizar operaciones sobre la información almacenada. Su estructura se basa en el formato de transporte de datos JSON y, además de la categoría clave-valor, el almacenamiento de información se asemeja a bb.dd. documentales, y a las bb.dd. orientadas a grafos. Mongo DB, Cassandra, Cosmos DB y Firestore, son las más conocidas y utilizadas actualmente.

Data Warehouse

Un Data Warehouse es un almacén electrónico donde generalmente una empresa u organización mantiene una gran cantidad de información. Los datos de un data warehouse deben almacenarse de forma segura, fiable, fácil de recuperar y fácil de administrar.

El concepto de data warehouse se originó en 1988 con el trabajo de los investigadores de IBM, Barry Devlin y Paul Murphy aunque el término data warehouse fue acuñado por William H. Inmon, el cual es conocido como el padre de Data Warehousing. Inmon describió un data warehouse como una colección de datos orientada a un tema específico, integrado, variante en el tiempo y no volátil, que soporta el proceso de toma de decisiones.



2 - My Sql

Volver al Inicio





Es un sistema de gestión de bases de datos relacional desarrollado bajo licencia dual: Licencia pública general/Licencia comercial por Oracle Corporation y está considerada como la base de datos de código abierto más popular del mundo, y una de las más populares en general junto a Oracle y Microsoft SQL Server, todo para entornos de desarrollo web.

A continuacion se encuentra un link con los instructivos para poder descargar e instalar My Sql:

Guia de Instalacion de My Sql

Glosario My Sql

Introduccion a Bases de Datos Relacionales

Las bases de datos relacionales se basan en el Modelo Relacional usando N cantidad de tablas para representar, tanto los datos, como las relaciones entre estos. Es poco frecuente encontrar casos de una bb.dd. con una sola tabla pero, en el caso de que se dé esta situación, se le denomina como Base de datos Plana. El modelo relacional, en el cual se apoyan estas bases de datos, almacenan la información en un conjunto de tablas, y a su vez, las aprovechan para representar tanto los datos como también las relaciones entre cada una de estas.



3 - Descripcion del proyecto

Volver al Inicio



Temática para el trabajo final:

Base de datos de una Empresa de Medicina Prepaga donde se va a hacer foco en un modelo de esquema copo de nieve.



4 - Descripción de las tablas

Volver al Inicio



Tablas

Cuando nos referimos a una tabla, estamos hablando específicamente de un tipo de elemento, que cumple el paradigma de Modelado de datos, y permite guardar la información denominada registros. Cada tabla se compone de Campos y Columnas. Cada campo debe ser único además de tener un tipo de dato asociado. Cada Columna de una tabla, define el total de la información que se almacena, comúnmente llamada registro. Un registro es el dato alojado en forma de fila, dentro de una tabla. Otro nombre que se le aplica comúnmente a los registros, es tupla. Las tablas también suelen ser llamadas, mucho más formalmente: entidad. Y, en algunos textos algo más antiguos: relación. Y al igual que el nombre tupla, aplicado a los registros, los campos o columnas también son denominados más formalmente como: atributos.

Estructura de Tablas

Es una estructura de tablas, las cuales a su vez se relacionan con otras tablas. Su principal característica es no poseer información repetida de forma innecesaria, lo que permite adicionar más información sin llegar a afectar la otra almacenada.

Ventajas del modelo Relacional

  • Favorece el proceso de normalización, el cual permite eliminar la redundancia de los datos.

  • Permite realizar consultas y obtener reportes de forma ágil y rápida por medio de SQL u otro lenguaje de base de datos estructurado.

  • Se pueden crear una o varias relaciones entre todas las tablas.

  • Ayuda a evitar la duplicidad de registros guardados.

  • Atomiza la información de la mejor forma posible.

  • Garantiza la integridad referencial.

  • Si un registro se relaciona con otro registro de otra tabla, no permite que el mismo sea eliminado. Asimismo, si se quiere borrar, también pasará con todos los datos relacionados.

Tipos de Relaciones

Uno a uno

Uno a uno

Uno a Muchos

Uno a Muchos

Muchos a Muchos

Muchos a Muchos

Logicas

  • Clave primaria PK: también llamada llave primaria o primary key, hace que el registro sea unívoco y obligatoriamente no nulo.

  • Clave foránea FK: también llamada foreign key, clave secundaria o clave externa, puede ser -o no- una clave primaria dentro de la tabla. Su característica es que es el punto de enlace con otra tabla donde ésta es primary key.

  • Clave índice: es un campo que facilita la búsqueda dentro de una tabla. Generalmente son campos primary key.

Tablas, descripción y atributos:

A continuación se detallaran cada una de las tablas que componen el DER con sus atributos y descripciones:



\[ \textbf{Tabla: Afiliado }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Afiliado\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Nombre\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Apellido\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Email\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Telefono\] \[INT \:NOT \:NULL \]
\[ Fecha\:Nacimiento\] \[DATE \:NOT \:NULL\]
\[ Domicilio\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Estado\:Civil\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Dni\] \[INT \:NOT \:NULL \]
\[ Id\:Ocupacion\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Plan\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]

Esta tabla contiene los atributos de los afiliados de la empresa de medicina prepaga, podemos observar el ID como Primary Key y por ultimo dos FK que hacen referencia a la ocupación del empleado y el plan. Estas serán dos tablas que se crean a parte para no repertir datos.

Un ejemplo sencillo: La ocupación puede ser empleado bajo relación de dependencia, monotributista, desempleado o jubilado. Estos datos se pondrán aparte en una tabla llamada ocupacion_afiliado y en la tabla afiliado solo de pondrá el numero de ID ya que al ir dando de alta a los afiliados en el sistema nos ahorrariamos espacio en la base de datos al no tener que repetir tantos caracteres por fila y solamente completar con el ID de ocupacion que viene de la tabla ocupacion_afiliado.



\[ \textbf{Tabla: Ocupacion Afiliado }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Ocupacion\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Ocupacion\] \[VARCHAR(50) \:NOT \:NULL \]

Esta tabla contiene los siguientes datos: Bajo relacion de dependencia, monotributista, desempleado y jubilado.



\[ \textbf{Tabla: Plan Afiliado }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Plan\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Nombre\] \[VARCHAR(50) \:NOT \:NULL \]

Esta tabla contiene los siguientes datos: Medium y Premium.



\[ \textbf{Tabla: Medico }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Medico\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Nombre\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Apellido\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Fecha\:Nacimiento\] \[DATE \:NOT \:NULL\]
\[ Domicilio\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Telefono\] \[INT \:NOT\: NULL \]

Esta tabla contiene los datos de los médicos que prestan su servicio a distintos centros médicos.



\[ \textbf{Tabla: Centro Medico }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Centro\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Nombre\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Dirección\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Teléfono\] \[INT \:NOT \:NULL\]
\[ Cod\:Postal\] \[INT \:NOT \:NULL\]
\[ Email\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Especialidad\:Principal\] \[VARCHAR(50) \:NOT \:NULL \]



\[ \textbf{Tabla: Consulta }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Consulta\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Consulta\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Diagnostico\] \[VARCHAR(50) \:NOT \:NULL \]

Esta tabla contiene los datos de las consultas de los afiliados y los diagnosticos posterior a la consulta.



\[ \textbf{Tabla: Turnos }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Turno\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Fecha\] \[DATE \:NOT \:NULL \]
\[ Id\: Medico\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\: Afiliado\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\: Consulta\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Centro\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]

Esta tabla contiene los datos del turno. Se conecta con varias tablas, entre ellas la de consulta. Una vez registrado el turno en la base de datos y luego de la consulta se completan los valores en la tabla consulta con el diagnostico final.



\[ \textbf{Tabla: Proveedor }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Proveedor\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Nombre\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Telefono\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Cuit\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Mail\] \[VARCHAR(50) \:NOT \:NULL \]

La tabla contiene todos los datos de los proveedores que se van dando de alta. No tiene muchos movimientos en general.



\[ \textbf{Tabla: Tipo Insumo }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Tipo\:Insumo\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Tipo\:Insumo\] \[VARCHAR(50) \:NOT \:NULL \]

Tabla que contiene datos sobre el tipo de insumo que compran los centros medicos. No tiene muchos movimientos.



\[ \textbf{Tabla: Insumos }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Insumo\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Nombre\] \[VARCHAR(50) \:NOT \:NULL \]
\[ Id\:Centro\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Tipo\:Insumo\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Proveedor\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]

Esta tabla contiene los datos de cada uno de los insumos.



\[ \textbf{Tabla: Centro Medico Insumos }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Centro\:Medico\:Insumos\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Id\:Centro\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Insumo\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]

Tabla que conecta de muchos a muchos los insumos con los centros medicos.



\[ \textbf{Tabla: Pagos }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Pagos\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Fecha\:Transaccion\] \[DATE\:NOT\:NULL\]
\[ Importe\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Medio\:Operacion\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Proveedor\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]

Tabla que registra todos los pagos de todos los centros médicos.



\[ \textbf{Tabla: Cobros }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Cobros\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Fecha\:Transaccion\] \[DATE\:NOT\:NULL\]
\[ Importe\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Medio\:Operacion\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]
\[ Id\:Afiliado\] \[INT \:NOT\: NULL\: FOREIGN \:KEY \]

Tabla que registra todos los cobros de todos los centros médicos.



\[ \textbf{Tabla: Medio Operacion }\] |
\[ \textbf{Atributos}\] \[ \textbf{Descripcion}\]
\[ Id\:Medio\:Operacion\] \[INT \:NOT\: NULL\: PRIMARY \:KEY\: AUTO\:INCREMENT\]
\[ Tipo\:Medio\:Operacion\] \[VARCHAR(50) \:NOT \:NULL \]

Tabla que registra todos medios financieros de pagos: Tarjeta de Debito, Crédito, Efectivo y Transferencias.

5 - Diagrama Entidad de Relacion

Volver al Inicio



Un diagrama entidad-relación, también conocido como modelo entidad relación o ERD, es un tipo de diagrama de flujo que ilustra cómo las “entidades”, como personas, objetos o conceptos, se relacionan entre sí dentro de un sistema. Los diagramas ER se usan a menudo para diseñar o depurar bases de datos relacionales en los campos de ingeniería de software, sistemas de información empresarial, educación e investigación.

En este link se puede elaborar un DER preliminar para luego trabajarlo en una base de datos.



Luego de crear las tablas en My Sql podemos ver el Reverse Engineer.

La ingeniería inversa o retroingeniería es el proceso llevado a cabo con el objetivo de obtener información o un diseño a partir de un producto, con el fin de determinar cuáles son sus componentes y de qué manera interactúan entre sí y cuál fue el proceso de fabricación.

La siguiente imagen representa el Reverse Engineer de la base de datos creada:



6 - Inner Join

Volver al Inicio



Inner Join es una sentencia de SQL para recuperar datos de varias tablas al mismo tiempo. En la siguiente imagen se muestra una consulta de todos los afiliados de la base de datos con los pagos de las cuotas acumuladas en el año.

Se tienen en cuenta las siguientes sentencias:



7 - Inserción de datos

Volver al Inicio



Metodologia utilizada: Los valores en cada una de las tablas se insertaron desde archivos .csv.

Los archivos son los siguientes:

Cada una de las tablas contiene 10 observaciones

Ejemplo tabla afiliados:

A continuación se adjuntan scripts de los pasos para importar los datos y MySql final:

8 - Vistas

Volver al Inicio

Una Vista SQL es básicamente una tabla virtual que se genera a partir de la ejecución de una o más consultas SQL, aplicada sobre una o más tablas. Su estructura corresponde a una serie de filas y columnas tal como encontramos en las tablas SQL, que almacenan la vista de la información tal como la definimos al crearla. Podemos guardar las Vistas con un nombre distintivo, para poder ejecutarlas cuantas veces consideremos necesario. Su almacenamiento se da en la sección Views de los objetos de la base de datos.

Beneficios

  • Privacidad de la información: los usuarios podrán ver solo aquellos datos que creamos convenientes mostrar.

  • Rendimiento de la bb.dd.: Crear queries sobre vistas complejas nos ahorra ejecutar una query pesada para llegar a la información.

  • Protección de datos: Aquellos usuarios que no poseen un entorno de pre-producción, las vistas evitan errores de borrado o alteración.

Scripts de Vistas

A continuación se adjuntan los scripts de las vistas

El código SQL de las vistas se encuentra en el siguiente link: Github Codigo



    1) Vista 1:

    Consulta y Diagnostico por Afiliado

    Esta vista permite guardarnos en una tabla aparte todos los turnos solicitados por los afiliados dados de alta en el sistema. Nos sirve para estar actualizados de los ultimos turnos ya que esta ordenada por fecha.





    2) Vista 2

    Que medico atendió a cada paciente:

    Esta vista creada nos sirve para visualizar forma rapida mediante su consulta sobre que medicos atendieron a que paciente en determinada fecha.





    3) Vista 3

    Que medico atiende en cada centro medico:

    Esta vista permite que podamos visualizar de forma rapida que medicos atienden en los centros medicos.





9 - Funciones

Volver al Inicio



Por definición es importante indicar que una función es un conjunto de instrucciones SQL que realizan una tarea específica de manera automática. Una función acepta entradas en forma de parámetros y devuelve un valor.

Las funciones personalizadas o almacenadas de Mysql permiten procesar y manipular datos de forma procedural y eficiente. Dichos datos son enviados a través de uno o más parámetros, al momento de invocar la función, y devueltos como resultado por esta misma.

Podemos crear funciones, a la medida de nuestra necesidad, combinando a las mismas con funciones ya existentes del lenguaje Mysql, para así obtener los resultados deseados tal como necesitamos.

Algunos de los beneficios a destacar:

  • Mejoran la integridad y seguridad de los datos

  • Optimizan el rendimiento de la base de datos

  • Otorgan una mejor lectura del código

A continuación se adjuntan los scripts de las funcioness

    1) Función 1:

    Función donde ingresamos un CHAR y nos devuelve un NUMERIC

    Ingresamos el diagnostico y nos regresa el porcentaje de pacientes con el diagnostico ingresado de nuestra base de datos

    En la parte inferior de la imagen adjunta se puede ver el resultado de la función una vez ingresado un CHAR.





    2) Función 2:

    Función que no requiere un valor de entrada.

    Esta funcion nos calcula automaticamente la diferencia entre ingresos y egresos de todos los centros medicos.

    En la parte inferior de la imagen adjunta se puede ver el resultado de la función una vez ingresado un CHAR.





10 - Stored Procedures

Volver al Inicio



Un Stored Procedure o Procedimiento Almacenado es un programa almacenado físicamente en una base de datos, creado para cumplir tareas específicas. Permite también establecer niveles de seguridad y manipular operaciones complejas o extensas del lado del servidor, evitando un ida y vuelta de datos que termine sobrecargando una red o servidor.

Su estructura es similar a las Funciones SQL que vimos la clase anterior pero, a diferencia de éstas, un Stored Procedure esta pensado para resolver desde una operación simple, hasta operaciones complejas que requieran modificar varias tablas y/o datos almacenados en una base de datos.

Algunos Beneficios:

  • El motor de bb.dd. controla las operaciones.
  • Se ejecuta en un servidor independiente.
  • Devuelve al usuario el resultado final, evitando sobrecargar su computadora con procesos.
  • Evita programar una lógica compleja del lado del usuario.
  • Minimiza los errores concentrando las operaciones.

A continuación se adjuntan los scripts de los Stored Procedures

    1) Stored Procedure 1:

    En el siguiente Stored Procedure insertamos valores en la tabla turno con el ID creado anteriormente en la tabla consulta.





    2) Stored Procedure 2:

    El siguiente Stored Procedure ejecuta un comando que nos permite ordenar la tabla que queramos por una variable.





11 - Triggers

Volver al Inicio



Un disparador o trigger es una funcionalidad que la base de datos ejecuta de forma automática cuando se realiza una operación de tipo Insert, Update o Delete en una tabla o vista, o cuando se ejecuta una consulta SQL sobre una tabla o vista.

Un trigger no puede existir sin una tabla asignada, podemos crear muchos triggers para una tabla.

Cuando un evento ya sea de tipo Insert, Update o Delete se realiza en una tabla que contiene uno o varios triggers, en la tabla que creamos que comienza con logs se ejecuta tambien ese evento modificando la tabla. Hay dos comandos que podemos utilizar, el Before y el After, con el primero cuando insertamos datos en la tabla principal primero ingresaran al triger y luego a la tabla principal, con el After ocurre todo lo contrario.

A continuación se adjuntan los scripts de los Triggers

    1) Triggers 1:

    En el siguiente Trigger se crea un log para la tabla afiliados y luego de insertar los datos en la tabla afiliados van a parar al trigger creado.





    Lo contrario sucede con la próxima imagen que es un Before.





    2) Triggers 2:

    En el siguiente Trigger se crea un log para la tabla consulta y antes de que se completen los nuevos valores para la tabla se completan en la tabla de logs con fecha, usuario, id y tipo de operación.

    Lo contrario sucede con la próxima imagen que es un Before.





    Lo contrario sucede con la próxima imagen que es un Before.





12 - Permisos

Volver al Inicio



Sentencias GRANT y REVOKE

Utilizando la sentencia GRANT podemos dar permisos al usuario creado para que pueda hacer los siguientes comandos sobre la base de datos o una tabla en particular: SELECT, INSERT, UPDATE y DELETE.

Con la sentencia REVOKE le quitamos permisos al usuario.

A continuación se adjuntan los scripts de los Usuarios creados y las sentencias